package ref;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import ref.OracleConn;
import ref.AAnalysisHandle;

public class QiXieBiaoZhun extends AAnalysisHandle {

	private String targetTable = "instrument_standard_directory";
	private static final String DATE_FORMAT_1 = "yyyy-MM-dd";
	private static final String DATE_FORMAT_2 = "yy-MM-dd";
	
	public String getHandleName() {
		return "器械标准";
	}

	public String getTargetTable() {
		return targetTable;
	}
	
	public void handleTransfer(String path, Connection oracleConn)throws Exception {
		PreparedStatement pstmt = null;
		try{
			int index = 0;
			int seq = 0;
			pstmt = oracleConn.prepareStatement("insert into "+ targetTable +" values(?,?,?,?,?,?,?,?,?,?)");
			oracleConn.setAutoCommit(false);
			InputStream is = new FileInputStream(new File(path));
			Workbook workbook = WorkbookFactory.create(is);
			Sheet sheet = workbook.getSheetAt(0);
			Row row ;
				for(int i = 0 ; i <= sheet.getLastRowNum(); i++) {
					row = sheet.getRow(i);
//					System.out.println("第"+i+"行");
					pstmt.setInt(1, ++seq);
					pstmt.setString(2, row.getCell(0).getStringCellValue().trim());
					pstmt.setString(3, row.getCell(1).getStringCellValue().trim());
					pstmt.setString(4, row.getCell(2).getStringCellValue().trim());
					pstmt.setString(5, row.getCell(3).getStringCellValue().trim());
					pstmt.setString(6, row.getCell(4).getStringCellValue().trim());
					if (!("").equals(row.getCell(5).getStringCellValue())) {
						SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT_1);
						SimpleDateFormat sdf2 = new SimpleDateFormat(DATE_FORMAT_2);
						java.util.Date date1 = sdf2.parse(row.getCell(5).getStringCellValue().replace(".", "-").trim());
						pstmt.setDate(7, Date.valueOf(sdf1.format(date1)));
					}else {
						pstmt.setString(7, "");
					}
					if (!("").equals(row.getCell(6).getStringCellValue())) {
						SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT_1);
						SimpleDateFormat sdf2 = new SimpleDateFormat(DATE_FORMAT_2);
						java.util.Date date1 = sdf2.parse(row.getCell(6).getStringCellValue().trim());
						pstmt.setDate(8, Date.valueOf(sdf1.format(date1)));
					} else {
						pstmt.setString(8, "");
					}
					
					pstmt.setString(9, row.getCell(7).getStringCellValue().trim());
					pstmt.setDate(10, new java.sql.Date(new java.util.Date().getTime()));
					pstmt.addBatch();
					index++;
					
					if(index % 4000 == 0) {
						pstmt.executeBatch();
						OracleConn.log("已处理【" + index + "】数据。");
					}
				}
			is.close();
			pstmt.executeBatch();
			oracleConn.commit();
			oracleConn.setAutoCommit(true);
			OracleConn.log("共处理【" + index + "】数据。");
		} catch (Exception e) {
			e.printStackTrace(System.err);
			try {
				oracleConn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace(System.err);
			}
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}
}